using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using SEAM.Data.Provider;

namespace SEAM.Data
{
    /// <summary>
    /// Summary description for DataAccess.
    /// </summary>
    public abstract class DataAccess : IDisposable
    {
        #region Member Variables
        protected string _ConnectionString;
        protected static IConnectionProvider _ConnectionProvider;
        protected int? _Timeout;
        #endregion

        #region Properties

        public virtual string ConnectionString
        {
            get { return _ConnectionString; }
            set { _ConnectionString = value; }
        }

        public static IConnectionProvider ConnectionProvider
        {
            get { return _ConnectionProvider; }
            set { _ConnectionProvider = value; }
        }

        public virtual int? Timeout
        {
            get { return _Timeout; }
            set { _Timeout = value; }
        }

        public static bool Initialized
        {
            get { return (ConnectionProvider != null); }
        }

        #endregion

        #region Constructors

        static DataAccess()
        {
            ConnectionProviderHandler.Initialize();
        }

        protected DataAccess()
            : this(null) { }

        protected DataAccess(string connectionString)
        {
            _ConnectionString = connectionString;

            try
            {
                if (string.IsNullOrEmpty(_ConnectionString) == false && Connection != null)
                    Connection.Open();
            }
            catch (Exception ex)
            {
                throw new DataAccessException("Error occured while trying to Open connection", null, null, ex);
            }
        }

        #endregion

        #region IDisposable Members

        public void Dispose()
        {
            try
            {
                if (Transaction != null)
                    RollbackTransaction();
            }
            finally
            {
                if (Transaction != null)
                    Transaction.Dispose();
                Transaction = null;
            }

            try
            {
                if (Connection.State != ConnectionState.Closed)
                    Connection.Close();
            }
            finally
            {
                Connection.Dispose();
            }
        }

        #endregion

        #region Abstract Functions/Properties

        protected abstract DbConnection Connection { get; }
        protected abstract DbTransaction Transaction { get; set; }
        protected abstract DbCommand GetCommand(CommandType cmdType, string cmdText, int? iTimeout);
        protected abstract DbDataAdapter GetAdapter(DbCommand cmdCommand);
        protected abstract DbParameter AddCommandParameter(DbCommand cmdCommand, DataAccessParam daParameter);

        #endregion

        #region SQL Execution Functions

        public virtual int ExecuteSQL(string strSQL)
        {
            DbCommand cmdCommand = null;
            int retVal;

            try
            {
                cmdCommand = GetCommand(CommandType.Text, strSQL);
                retVal = cmdCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new DataAccessException("Error during ExecuteSQL.", strSQL, null, ex);
            }
            finally
            {
                if (cmdCommand != null)
                    cmdCommand.Dispose();
            }

            return retVal;
        }

        public virtual DataTable Query(string strSQL)
        {
            DbCommand cmdCommand = null;
            DbDataAdapter adapter = null;
            DataTable retTable = null;

            try
            {
                cmdCommand = GetCommand(CommandType.Text, strSQL);
                adapter = GetAdapter(cmdCommand);
                retTable = new DataTable();
                adapter.Fill(retTable);
            }
            catch (Exception ex)
            {
                throw new DataAccessException("Error during Query.", strSQL, null, ex);
            }
            finally
            {
                if (adapter != null)
                    adapter.Dispose();

                if (cmdCommand != null)
                    cmdCommand.Dispose();
            }
            return retTable;
        }

        public virtual ReturnCode ExecuteStoredProcedure(string sName, DataAccessParam[] arrParameters)
        {
            DbCommand cmdCommand = null;
            ReturnCode retVal = ReturnCode.Unknown;

            try
            {
                cmdCommand = GetCommand(CommandType.StoredProcedure, sName);
                SetCommandParams(cmdCommand, arrParameters);
                cmdCommand.ExecuteNonQuery();
                retVal = UpdateCommandParams(cmdCommand, arrParameters);
            }
            catch (Exception ex)
            {
                throw new DataAccessException("Error during ExecuteStoredProcedure.", sName, arrParameters, ex);
            }
            finally
            {
                if (cmdCommand != null)
                    cmdCommand.Dispose();
            }
            return retVal;
        }

        public virtual ReturnCode ExecuteStoredProcedure(string sName, DataAccessParam[] arrParameters, out DataSet dsOutput)
        {
            DbCommand cmdCommand = null;
            DbDataAdapter adapter = null;
            dsOutput = new DataSet();
            ReturnCode retVal = ReturnCode.Unknown;

            try
            {
                cmdCommand = GetCommand(CommandType.StoredProcedure, sName);
                SetCommandParams(cmdCommand, arrParameters);
                adapter = GetAdapter(cmdCommand);
                adapter.Fill(dsOutput);
                retVal = UpdateCommandParams(cmdCommand, arrParameters);
            }
            catch (Exception ex)
            {
                throw new DataAccessException("Error during ExecuteStoredProcedure.", sName, arrParameters, ex);
            }
            finally
            {
                if (adapter != null)
                    adapter.Dispose();

                if (cmdCommand != null)
                    cmdCommand.Dispose();
            }

            return retVal;
        }

        public virtual ReturnCode ExecuteStoredProcedure(string sName, DataAccessParam[] arrParameters, out DataTable dtOutput)
        {
            DbCommand cmdCommand = null;
            DbDataAdapter adapter = null;
            dtOutput = new DataTable();
            ReturnCode retVal = ReturnCode.Unknown;

            try
            {
                cmdCommand = GetCommand(CommandType.StoredProcedure, sName);
                SetCommandParams(cmdCommand, arrParameters);
                adapter = GetAdapter(cmdCommand);
                adapter.Fill(dtOutput);
                retVal = UpdateCommandParams(cmdCommand, arrParameters);
            }
            catch (Exception ex)
            {
                throw new DataAccessException("Error during ExecuteStoredProcedure.", sName, arrParameters, ex);
            }
            finally
            {
                if (adapter != null)
                    adapter.Dispose();

                if (cmdCommand != null)
                    cmdCommand.Dispose();
            }
            return retVal;
        }

        public virtual ReturnCode ExecuteStoredProcedure(string sName, DataAccessParam[] arrParameters, out DbDataReader drOutput)
        {
            DbCommand cmdCommand = null;
            ReturnCode retVal = ReturnCode.Unknown;

            try
            {
                cmdCommand = GetCommand(CommandType.StoredProcedure, sName);
                SetCommandParams(cmdCommand, arrParameters);
                drOutput = cmdCommand.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw new DataAccessException("Error during ExecuteStoredProcedure.", sName, arrParameters, ex);
            }
            finally
            {
                if (cmdCommand != null)
                    cmdCommand.Dispose();
            }
            return retVal;
        }

        #endregion

        #region Helper Functions

        protected virtual DbCommand GetCommand(CommandType cmdType, string cmdText)
        {
            return GetCommand(cmdType, cmdText, Timeout);
        }

        protected virtual DbParameter SetReturnValueParameter(DbCommand cmdCommand)
        {
            return null;
        }

        protected virtual void SetCommandParams(DbCommand cmdCommand, DataAccessParam[] arrParameters)
        {
            // Override timeout if it was specified
            if (Timeout.HasValue)
                cmdCommand.CommandTimeout = Timeout.Value;

            // Set parameters if needed for the command
            if (arrParameters == null || arrParameters.Length <= 0)
                return;

            bool IsReturnParamSpecified = false;

            for (int i = 0; i < arrParameters.Length; i++)
            {
                DbParameter curParam = AddCommandParameter(cmdCommand, arrParameters[i]);
                curParam.Direction = arrParameters[i].ParamDirection;
                if (arrParameters[i].IsSizeSpecified)
                    curParam.Size = arrParameters[i].Size.Value;
                if (arrParameters[i].IsValueSpecified)
                    curParam.Value = arrParameters[i].Value;
                else
                    curParam.Value = DBNull.Value;

                if (arrParameters[i].ParamDirection == ParameterDirection.ReturnValue && arrParameters[i].Type == ParamType.Integer)
                    IsReturnParamSpecified = true;
            }

            if (!IsReturnParamSpecified)
            {
                DbParameter retCodeParam = SetReturnValueParameter(cmdCommand);
                if (retCodeParam != null)
                {
                    retCodeParam.Direction = ParameterDirection.ReturnValue;
                    retCodeParam.Value = DBNull.Value;
                }
            }
        }

        protected virtual int UpdateCommandParams(DbCommand cmdCommand, DataAccessParam[] arrParameters)
        {
            int retVal = ReturnCode.Unknown;

            if (arrParameters != null)
            {
                for (int i = 0; i < arrParameters.Length; i++)
                {
                    DataAccessParam aParam = arrParameters[i];
                    if (aParam.ParamDirection == ParameterDirection.Output || aParam.ParamDirection == ParameterDirection.ReturnValue || aParam.ParamDirection == ParameterDirection.InputOutput)
                    {
                        aParam.Value = cmdCommand.Parameters[i].Value;
                        if (aParam.ParamDirection == ParameterDirection.ReturnValue && aParam.Type == ParamType.Integer)
                            retVal = (int)aParam.Value;
                    }
                }

                if (retVal == ReturnCode.Unknown && cmdCommand.Parameters.Count > arrParameters.Length && cmdCommand.Parameters[arrParameters.Length].Direction == ParameterDirection.ReturnValue)
                    retVal = (int)cmdCommand.Parameters[arrParameters.Length].Value;
            }

            return retVal;
        }

        protected virtual String GetParameterString(DataAccessParam[] arrParameters)
        {
            if (arrParameters == null)
                return null;

            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < arrParameters.Length; i++)
                sb.Append("\t" + arrParameters[i].ToString() + "\r\n");
            return sb.ToString();
        }

        #endregion

        #region Transaction Support Function

        public void BeginTransaction()
        {
            Transaction = Connection.BeginTransaction();
        }

        public void CommitTransaction()
        {
            if (Transaction != null)
                Transaction.Commit();
        }

        public void RollbackTransaction()
        {
            if (Transaction != null)
                Transaction.Rollback();
        }

        #endregion

        #region Parameter Fetching Methods

        public static DataAccessParam GetParameter(string sName, ParamType ptType)
        {
            return new DataAccessParam(sName, ptType);
        }

        public static DataAccessParam GetParameter(string sName, ParamType ptType, object objValue)
        {
            return new DataAccessParam(sName, ptType, objValue);
        }

        public static DataAccessParam GetParameter(string sName, ParamType ptType, int iSize)
        {
            return new DataAccessParam(sName, ptType, iSize);
        }

        public static DataAccessParam GetParameter(string sName, ParamType ptType, int iSize, object objValue)
        {
            return new DataAccessParam(sName, ptType, iSize, objValue);
        }

        public static DataAccessParam GetOutputParameter(string sName, ParamType ptType)
        {
            DataAccessParam param = new DataAccessParam(sName, ptType);
            param.ParamDirection = ParameterDirection.Output;
            return param;
        }

        public static DataAccessParam GetOutputParameter(string sName, ParamType ptType, int iSize)
        {
            DataAccessParam param = new DataAccessParam(sName, ptType, iSize);
            param.ParamDirection = ParameterDirection.Output;
            return param;
        }

        protected static internal DataAccessParam GetReturnParameter(string sName, ParamType ptType)
        {
            DataAccessParam param = new DataAccessParam(sName, ptType);
            param.ParamDirection = ParameterDirection.ReturnValue;
            return param;
        }

        protected static internal DataAccessParam GetReturnParameter(string sName, ParamType ptType, int iSize)
        {
            DataAccessParam param = new DataAccessParam(sName, ptType, iSize);
            param.ParamDirection = ParameterDirection.ReturnValue;
            return param;
        }

        #endregion

        #region Static Functions

        #region ExecuteSQLStatement Functions

        public static int ExecuteSQLStatement(string sSQL, int? iTimeout, params object[] objDBConn)
        {
            using (DataAccess daConn = GetDataAccess(objDBConn))
            {
                daConn.Timeout = iTimeout;
                return daConn.ExecuteSQL(sSQL);
            }
        }

        public static int ExecuteSQLStatement(string sSQL, params object[] objDBConn)
        {
            return DataAccess.ExecuteSQLStatement(sSQL, objDBConn);
        }

        #endregion

        #region ExecuteQuery Functions

        public static DataTable ExecuteQuery(string sSQL, int? iTimeout, params object[] objDBConn)
        {
            using (DataAccess daConn = GetDataAccess(objDBConn))
            {
                daConn.Timeout = iTimeout;
                return daConn.Query(sSQL);
            }
        }

        public static DataTable ExecuteQuery(string sSQL, params object[] objDBConn)
        {
            return DataAccess.ExecuteQuery(sSQL, null, objDBConn);
        }

        public static List<T> ExecuteQuery<T>(string sSQL, int? iTimeout, params object[] objDBConn)
            where T : new()
        {
            return DataAccess.ExecuteQuery(sSQL, iTimeout, objDBConn).Cast<T>();
        }

        public static List<T> ExecuteQuery<T>(string sSQL, params object[] objDBConn)
            where T : new()
        {
            return DataAccess.ExecuteQuery<T>(sSQL, null, objDBConn);
        }

        #endregion

        #region ExecuteStoredProcedure Functions

        public static ReturnCode ExecuteStoredProcedure(string strName, DataAccessParam[] arrParameters, int? iTimeout, out DataTable dtOutput, params object[] objDBConn)
        {
            using (DataAccess daConn = GetDataAccess(objDBConn))
            {
                daConn.Timeout = iTimeout;
                return daConn.ExecuteStoredProcedure(strName, arrParameters, out dtOutput);
            }
        }

        public static ReturnCode ExecuteStoredProcedure(string strName, DataAccessParam[] arrParameters, out DataTable dtOutput, params object[] objDBConn)
        {
            return DataAccess.ExecuteStoredProcedure(strName, arrParameters, null, out dtOutput, objDBConn);
        }

        public static ReturnCode ExecuteStoredProcedure<T>(string strName, DataAccessParam[] arrParameters, int? iTimeout, out List<T> lstOutput, params object[] objDBConn)
            where T : new()
        {
            DataTable dtOutput;
            ReturnCode retVal = DataAccess.ExecuteStoredProcedure(strName, arrParameters, iTimeout, out dtOutput, objDBConn);
            lstOutput = dtOutput.Cast<T>();
            return retVal;
        }

        public static ReturnCode ExecuteStoredProcedure<T>(string strName, DataAccessParam[] arrParameters, out List<T> lstOutput, params object[] objDBConn)
            where T : new()
        {
            return DataAccess.ExecuteStoredProcedure<T>(strName, arrParameters, null, out lstOutput, objDBConn);
        }

        #endregion

        public static DataAccess GetDataAccess(params object[] objDBConn)
        {
            if (ConnectionProvider == null)
                throw new DataAccessException("DataAccess provider has not been initizlized properly.");
            return ConnectionProvider.GetDataAccess(objDBConn);
        }

        #endregion

    }
}
